from flask import render_template, request, redirect, url_for, session, flash, current_app
import pandas as pd
import numpy as np
import os
from datetime import datetime, timedelta
from werkzeug.utils import secure_filename
import pickle
from app import app, data_cache, model_cache, get_from_cache, set_to_cache, clear_cache
from collections import defaultdict
from prophet import Prophet
import io
from scripts.single_predict import single_predict_view

# --- 全局变量和路径设置 ---
UPLOAD_FOLDER = os.path.join(os.path.dirname(app.root_path), 'uploads')
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
ALLOWED_EXTENSIONS = {'csv', 'xlsx'}

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

# --- 模型加载优化 ---
def load_model():
    """加载聚合模型（使用缓存）"""
    # 检查缓存
    if model_cache['aggregate_model'] is not None:
        return model_cache['aggregate_model']
    
    # 如果正在预加载，等待一下
    if model_cache['loading']:
        print("模型正在预加载中，请稍候...")
        import time
        for i in range(10):  # 最多等待10秒
            time.sleep(1)
            if model_cache['aggregate_model'] is not None:
                return model_cache['aggregate_model']
    
    model_path = current_app.config.get('MODEL_PATH')
    if not model_path:
        print("错误: 模型路径未在 config.py 中配置 (MODEL_PATH)。")
        flash("关键错误: 系统未配置有效的预测模型路径。", "danger")
        return None, None
    
    base_dir = os.path.dirname(current_app.root_path)
    absolute_path = os.path.join(base_dir, model_path)

    if not os.path.exists(absolute_path):
        print(f"错误: 在配置的路径中找不到模型文件: {absolute_path}")
        flash(f"配置错误: 在指定路径 {model_path} 未找到模型文件。", "danger")
        return None, None
    
    try:
        with open(absolute_path, 'rb') as f:
            model_payload = pickle.load(f)
            model = model_payload['model']
            target_col = model_payload.get('target', 'y')
            print(f"成功从 {absolute_path} 加载聚合模型，目标列: {target_col}")
            # 缓存模型
            model_cache['aggregate_model'] = (model, target_col)
            return model, target_col
    except Exception as e:
        print(f"加载模型时发生错误: {e}")
        flash(f"模型加载失败: {e}", "danger")
        return None, None

def load_product_models():
    """加载商品级别模型（使用缓存和延迟加载）"""
    # 检查缓存
    if model_cache['product_models'] is not None:
        return model_cache['product_models']
    
    # 如果正在预加载，等待一下
    if model_cache['loading']:
        print("商品级别模型正在预加载中，请稍候...")
        import time
        for i in range(30):  # 最多等待30秒（因为文件较大）
            time.sleep(1)
            if model_cache['product_models'] is not None:
                return model_cache['product_models']
    
    product_models_path = current_app.config.get('PRODUCT_MODELS_PATH')
    if not product_models_path:
        print("警告: 商品级别模型路径未配置，将使用聚合模型。")
        return None
    
    base_dir = os.path.dirname(current_app.root_path)
    absolute_path = os.path.join(base_dir, product_models_path)

    if not os.path.exists(absolute_path):
        print(f"警告: 商品级别模型文件不存在: {absolute_path}")
        return None
    
    try:
        print("正在加载商品级别模型（首次加载可能需要几秒钟）...")
        with open(absolute_path, 'rb') as f:
            product_payload = pickle.load(f)
            product_models = product_payload.get('models', {})
            print(f"成功加载 {len(product_models)} 个商品级别模型")
            # 缓存模型
            model_cache['product_models'] = product_models
            return product_models
    except Exception as e:
        print(f"加载商品级别模型时发生错误: {e}")
        return None

def get_product_model(product_id):
    """获取特定商品的模型（延迟加载）"""
    product_models = load_product_models()
    lookup_id = str(product_id)
    # 增加详细日志
    print(f"[DEBUG] 查找商品ID: {repr(lookup_id)}")
    if product_models:
        print(f"[DEBUG] 商品级别模型字典前5个key: {[repr(k) for k in list(product_models.keys())[:5]]}")
        if lookup_id in product_models:
            print(f"[DEBUG] 命中商品级别模型: {lookup_id}")
            return product_models[lookup_id]
        else:
            print(f"[DEBUG] 未命中商品级别模型: {lookup_id}")
    else:
        print("[DEBUG] 商品级别模型未加载或为空")
    return None

# --- 数据处理与预测 ---
def generate_product_specific_analysis(product_id, product_data, days_to_predict, current_stock, lead_time, safety_factor):
    """为特定商品生成分析结果"""
    print(f"[DEBUG] generate_product_specific_analysis: product_id={repr(product_id)}")
    product_model_info = get_product_model(product_id)
    if product_model_info:
        print(f"[DEBUG] 使用商品级别模型预测商品 {repr(product_id)}")
        return _analyze_with_product_model(product_id, product_model_info, 
                                         days_to_predict, current_stock, lead_time, safety_factor)
    print(f"[DEBUG] 使用聚合模型预测商品 {repr(product_id)}")
    model, target_col = load_model()
    if not model:
        return [], {}, "模型加载失败", True
    return _analyze_with_aggregate_model(product_data, model, target_col, 
                                       days_to_predict, current_stock, lead_time, safety_factor)

def _analyze_with_product_model(product_id, product_model_info, days_to_predict, current_stock, lead_time, safety_factor):
    """使用商品级别模型进行分析（已优化补货逻辑）"""
    try:
        model = product_model_info['sales_model']
        product_name = product_model_info.get('product_name', f'商品_{product_id}')
        
        # 扩展预测期以覆盖最后的补货周期，修正为2*lead_time以正确计算目标库存
        future = model.make_future_dataframe(periods=days_to_predict + 2 * lead_time)
        forecast = model.predict(future)
        
        forecast_future = forecast[forecast['ds'] > datetime.now()].copy()
        if forecast_future.empty:
            return [], {}, "无法生成未来预测", True
        
        daily_results = []
        stock_level = float(current_stock)
        incoming_deliveries = defaultdict(int)

        full_forecast_dict = forecast_future.set_index('ds')['yhat'].to_dict()
        sim_dates = forecast_future['ds'].head(days_to_predict)

        # ====== 新增：计算周期阶段划分 ======
        # 计算每个阶段的长度（使用整数除法处理不能被均分的情况）
        rising_days = days_to_predict // 3
        peak_days = days_to_predict // 3
        decline_days = days_to_predict - rising_days - peak_days

        # 设置阶段边界
        rising_end = rising_days
        peak_end = rising_days + peak_days

        # 阶段需求统计
        phase_demand = {
            'rising': 0,
            'peak': 0,
            'decline': 0
        }

        # 初始化阶段名称
        phase_names = {
            'rising': '上升期',
            'peak': '爆发期',
            'decline': '衰退期'
        }
        # ====== 阶段计算结束 ======

        for i ,date in enumerate(sim_dates):

            # ====== 新增：确定当前日期所处的阶段 ======
            if i < rising_end:
                current_phase = 'rising'
            elif i < peak_end:
                current_phase = 'peak'
            else:
                current_phase = 'decline'
            # ====== 阶段判断结束 ======


            # 1. 货物到达
            stock_level += incoming_deliveries.pop(date.date(), 0)

            # 2. 计算在途库存
            stock_on_order = sum(incoming_deliveries.values())

            # 3. 发生销售
            predicted_demand = max(0, float(np.round(full_forecast_dict.get(date, 0))))
            stock_before_sale = stock_level
            stock_after_sale = stock_before_sale - predicted_demand

            # 记录阶段需求
            phase_demand[current_phase] += predicted_demand

            # 4. 计算警戒线(s)和目标线(S)
            # s = ROP, 是补货的触发器
            lead_time_window_dates = pd.to_datetime([date + timedelta(days=d) for d in range(1, lead_time + 1)])
            demand_during_lead_time = sum(max(0, np.round(full_forecast_dict.get(d, 0))) for d in lead_time_window_dates)
            safety_stock_for_lead_time = demand_during_lead_time * (safety_factor - 1.0)
            reorder_point = demand_during_lead_time + safety_stock_for_lead_time

            # 5. 基于"分批进货"策略进行补货决策
            inventory_position = stock_after_sale + stock_on_order
            order_qty = 0
            suggestion = "无需补货"
            # 当库存头寸低于警戒线时，触发补货
            if inventory_position < reorder_point:
                # 订单量只将库存头寸补足到"再订货点"水平
                order_qty = max(0, int(np.ceil(reorder_point - inventory_position)))
                if order_qty > 0:
                    suggestion = f"建议补货 {order_qty} 件"
                    arrival_date = (date + timedelta(days=lead_time)).date()
                    incoming_deliveries[arrival_date] += order_qty

            # 6. 更新状态 (状态判断仍基于短期安全库存)
            status = "库存充足"
            if stock_after_sale <= 0:
                status = "缺货"
            elif stock_after_sale < safety_stock_for_lead_time:
                status = "库存紧张"

            daily_results.append({
                'date': date.strftime('%Y-%m-%d'),
                'predicted_demand': int(predicted_demand),
                'stock_before': int(np.round(stock_before_sale)),
                'stock_after': int(np.round(stock_after_sale)),
                'safety_stock': int(np.round(safety_stock_for_lead_time)), # 真正的安全库存
                'reorder_point': int(np.round(reorder_point)),    # 再订货点
                'status': status,
                'suggestion': suggestion,
                'order_qty': order_qty,
                # ====== 新增：添加阶段信息 ======
                'phase': current_phase,
                'phase_name': phase_names[current_phase]
                # ====== 阶段信息添加结束 ======
            })

            stock_level = stock_after_sale
            # ====== 新增：计算阶段需求占比 ======
            total_demand = sum(phase_demand.values())
            phase_percentages = {}
            for phase, demand in phase_demand.items():
                if total_demand > 0:
                    percentage = (demand / total_demand) * 100
                    phase_percentages[f'{phase}_percentage'] = round(percentage, 1)
                else:
                    phase_percentages[f'{phase}_percentage'] = 0.0
            # ====== 阶段需求统计结束 ======
        summary = {
            'total_demand': int(sum(d['predicted_demand'] for d in daily_results)),
            'total_orders': int(sum(d['order_qty'] for d in daily_results)),
            'model_type': '商品级别模型',
            'product_name': product_name
        }

        return daily_results, summary, None, False

    except Exception as e:
        print(f"商品级别模型预测失败: {e}")
        return [], {}, f"商品级别模型预测失败: {e}", True


def _analyze_with_aggregate_model(product_data, model, target_col, days_to_predict, current_stock, lead_time,
                                  safety_factor):
    """使用聚合模型进行分析（已优化补货逻辑）"""
    if not model:
        print("错误: 预测函数收到了一个无效的模型。")
        return [], {}, None, False

    # 扩展预测期以覆盖最后的补货周期，修正为2*lead_time以正确计算目标库存
    future = model.make_future_dataframe(periods=days_to_predict + 2 * lead_time)
    forecast = model.predict(future)

    forecast_future = forecast[forecast['ds'] > datetime.now()].copy()
    if forecast_future.empty:
        return [], {}, "无法生成未来预测", True

    daily_results = []
    stock_level = float(current_stock)
    incoming_deliveries = defaultdict(int)

    full_forecast_dict = forecast_future.set_index('ds')['yhat'].to_dict()
    sim_dates = forecast_future['ds'].head(days_to_predict)

    # ====== 新增：计算周期阶段划分 ======
    # 计算每个阶段的长度（使用整数除法处理不能被均分的情况）
    rising_days = days_to_predict // 3
    peak_days = days_to_predict // 3
    decline_days = days_to_predict - rising_days - peak_days

    # 设置阶段边界
    rising_end = rising_days
    peak_end = rising_days + peak_days

    # 阶段需求统计
    phase_demand = {
        'rising': 0,
        'peak': 0,
        'decline': 0
    }

    # 初始化阶段名称
    phase_names = {
        'rising': '上升期',
        'peak': '爆发期',
        'decline': '衰退期'
    }
    # ====== 阶段计算结束 ======
    t_models = 153
    for i,date in enumerate(sim_dates):

        # ====== 新增：确定当前日期所处的阶段 ======
        if i < rising_end:
            current_phase = 'rising'
        elif i < peak_end:
            current_phase = 'peak'
        else:
            current_phase = 'decline'
        # ====== 阶段判断结束 ======

        # 1. 货物到达
        stock_level += incoming_deliveries.pop(date.date(), 0)

        # 2. 计算在途库存
        stock_on_order = sum(incoming_deliveries.values())

        # 3. 发生销售
        predicted_demand = max(0, float(np.round(full_forecast_dict.get(date, 0))))/t_models
        stock_before_sale = stock_level
        stock_after_sale = stock_before_sale - predicted_demand

        # 记录阶段需求
        phase_demand[current_phase] += predicted_demand

        # 4. 计算警戒线(s)和目标线(S)
        # s = ROP, 是补货的触发器
        lead_time_window_dates = pd.to_datetime([date + timedelta(days=d) for d in range(1, lead_time + 1)])
        demand_during_lead_time = sum(max(0, np.round(full_forecast_dict.get(d, 0))) for d in lead_time_window_dates)
        safety_stock_for_lead_time = demand_during_lead_time * (safety_factor - 1.0) /t_models
        reorder_point = (demand_during_lead_time + safety_stock_for_lead_time)/t_models

        # 5. 基于"分批进货"策略进行补货决策
        inventory_position = stock_after_sale + stock_on_order
        order_qty = 0
        suggestion = "无需补货"
        # 当库存头寸低于警戒线时，触发补货
        if inventory_position < reorder_point:
            # 订单量只将库存头寸补足到"再订货点"水平
            order_qty = max(0, int(np.ceil(reorder_point - inventory_position)))
            if order_qty > 0:
                suggestion = f"建议补货 {order_qty} 件"
                arrival_date = (date + timedelta(days=lead_time)).date()
                incoming_deliveries[arrival_date] += order_qty
        
        # 6. 更新状态 (状态判断仍基于真实的安全库存水平)
        status = "库存充足"
        if stock_after_sale <= 0:
            status = "缺货"
        elif stock_after_sale < safety_stock_for_lead_time:
            status = "库存紧张"

        daily_results.append({
            'date': date.strftime('%Y-%m-%d'),
            'predicted_demand': int(predicted_demand),
            'stock_before': int(np.round(stock_before_sale)),
            'stock_after': int(np.round(stock_after_sale)),
            'safety_stock': int(np.round(safety_stock_for_lead_time)), # 真正的安全库存
            'reorder_point': int(np.round(reorder_point)),    # 再订货点
            'status': status,
            'suggestion': suggestion,
            'order_qty': order_qty,
            # ====== 新增：添加阶段信息 ======
            'phase': current_phase,
            'phase_name': phase_names[current_phase]
            # ====== 阶段信息添加结束 ======
        })
        
        stock_level = stock_after_sale
        # ====== 新增：计算阶段需求占比 ======
        total_demand = sum(phase_demand.values())
        phase_percentages = {}
        for phase, demand in phase_demand.items():
            if total_demand > 0:
                percentage = (demand / total_demand) * 100
                phase_percentages[f'{phase}_percentage'] = round(percentage, 1)
            else:
                phase_percentages[f'{phase}_percentage'] = 0.0
        # ====== 阶段需求统计结束 ======
    
    summary = {
        'total_demand': int(sum(d['predicted_demand'] for d in daily_results)),
        'total_orders': int(sum(d['order_qty'] for d in daily_results)),
        'model_type': '聚合模型'
    }

    return daily_results, summary, None, False

# --- 路由 ---
@app.route('/', methods=['GET', 'POST'])
def index():
    """首页只保留系统介绍和跳转按钮"""
    return render_template('index.html')

@app.route('/product_data_manage', methods=['GET', 'POST'])
def product_data_manage():
    """商品数据管理页，首次加载Excel并缓存，后续直接用缓存"""
    cache_key = 'product_data_list'
    import_path = os.path.join(os.path.dirname(app.root_path), 'data', 'model_data', 'data_train_list.xlsx')
    df = get_from_cache(cache_key)
    if df is None:
        if os.path.exists(import_path):
            try:
                df = pd.read_excel(import_path, dtype={'商品ID': str})
                set_to_cache(cache_key, df)
            except Exception as e:
                flash(f'商品数据文件读取失败: {e}', 'danger')
                df = None
        else:
            flash('未找到商品数据文件 data/model_data/data_train_list.xlsx', 'danger')
            df = None

    page_data = []
    total_pages = 0
    search_query = request.args.get('q', '').strip()

    if df is not None:
        # 搜索过滤
        if search_query:
            search_mask = (
                df['商品ID'].astype(str).str.contains(search_query, na=False, case=False)
            )
            df_filtered = df[search_mask]
        else:
            df_filtered = df
        # 按商品ID和日期排序，保留每个商品最新一条
        if '统计日期' in df_filtered.columns:
            df_sorted = df_filtered.sort_values(['商品ID', '统计日期'], ascending=[True, False])
        else:
            df_sorted = df_filtered
        df_unique = df_sorted.drop_duplicates(subset=['商品ID'], keep='first')
        # 只显示前100条记录
        df_for_render = df_unique.head(100).fillna('')
        paginated_data = df_for_render.to_dict('records')
        page_data = paginated_data
        total_pages = 1
    else:
        search_query = ""

    return render_template('product_data_manage.html',
                           table_data=page_data,
                           total_pages=total_pages,
                           current_page=1,
                           has_file=(df is not None),
                           search_query=search_query)

@app.route('/detail/<item_id>')
def detail_id(item_id):
    """商品详情页，直接用缓存的商品数据"""
    cache_key = 'product_data_list'
    df = get_from_cache(cache_key)
    if df is None:
        # 兜底：重新加载
        import_path = os.path.join(os.path.dirname(app.root_path), 'data', 'model_data', 'data_train_list.xlsx')
        if os.path.exists(import_path):
            try:
                df = pd.read_excel(import_path, dtype={'商品ID': str})
                set_to_cache(cache_key, df)
            except Exception as e:
                flash(f'商品数据文件读取失败: {e}', 'danger')
                return redirect(url_for('product_data_manage'))
        else:
            flash('未找到商品数据文件 data/model_data/data_train_list.xlsx', 'danger')
            return redirect(url_for('product_data_manage'))

    # 假设 '商品ID' 是唯一标识符
    product_data = df[df['商品ID'].astype(str) == str(item_id)]
    if product_data.empty:
        flash("未找到商品")
        return redirect(url_for('product_data_manage'))
    
    # 确保转换为Python原生类型
    product_info = {}
    for key, value in product_data.iloc[0].to_dict().items():
        if hasattr(value, 'item'):  # NumPy标量
            product_info[key] = value.item()
        elif hasattr(value, 'to_pydatetime'):  # Pandas Timestamp
            product_info[key] = value.to_pydatetime().strftime('%Y-%m-%d %H:%M:%S')
        else:
            product_info[key] = value
    
    results = get_from_cache(f'results_{item_id}')
    
    # 检查是否有商品级别模型（使用优化的检查方式）
    has_product_model = get_product_model(item_id) is not None

    return render_template('detail.html', 
                           product_info=product_info, 
                           item_id=item_id, 
                           results=results,
                           has_product_model=has_product_model)

@app.route('/predict/<item_id>', methods=['POST'])
def predict(item_id):
    """预测接口，统一用缓存的商品数据"""
    cache_key = 'product_data_list'
    df = get_from_cache(cache_key)
    if df is None:
        # 兜底：重新加载
        import_path = os.path.join(os.path.dirname(app.root_path), 'data', 'model_data', 'data_train_list.xlsx')
        if os.path.exists(import_path):
            try:
                df = pd.read_excel(import_path, dtype={'商品ID': str})
                set_to_cache(cache_key, df)
            except Exception as e:
                flash(f'商品数据文件读取失败: {e}', 'danger')
                return redirect(url_for('product_data_manage'))
        else:
            flash("数据文件已过期，请联系管理员", "warning")
            return redirect(url_for('product_data_manage'))

    product_data = df[df['商品ID'].astype(str) == str(item_id)]
    if product_data.empty:
        flash("未找到商品", "danger")
        return redirect(url_for('detail_id', item_id=item_id))

    try:
        current_stock = int(request.form.get('current_stock', 0))
        days_to_predict = int(request.form.get('days_to_predict', 30))
        lead_time = int(request.form.get('lead_time', 7))
        safety_factor = float(request.form.get('safety_factor', 1.2))
    except (ValueError, TypeError):
        flash("输入的参数无效，请检查。", "danger")
        return redirect(url_for('detail_id', item_id=item_id))
        
    daily_results, summary, warning, is_zero = generate_product_specific_analysis(
        item_id, product_data, days_to_predict, current_stock, lead_time, safety_factor
    )

    set_to_cache(f'results_{item_id}', {
        'daily': daily_results,
        'summary': summary,
        'warning': warning,
        'is_zero': is_zero,
        'params': {
            'current_stock': current_stock,
            'days_to_predict': days_to_predict,
            'lead_time': lead_time,
            'safety_factor': safety_factor
        },
        'plot_url': None  # 商品详情页暂时没有图表，设为None
    })
    
    return redirect(url_for('detail_id', item_id=item_id))

@app.route('/manual')
def manual():
    """使用说明"""
    return render_template('manual.html')

@app.route('/clear_session')
def clear_session():
    """清除会话和数据缓存"""
    session.clear()
    # 清除数据缓存，但保留模型缓存
    # model_cache 是独立的，所以直接清空 data_cache 是安全的
    clear_cache()
    flash("会话和数据缓存已清除", "info")
    return redirect(url_for('product_data_manage'))

@app.route('/model_status')
def model_status():
    """检查模型加载状态"""
    status = {
        'aggregate_model_loaded': model_cache['aggregate_model'] is not None,
        'product_models_loaded': model_cache['product_models'] is not None,
        'loading': model_cache['loading'],
        'last_loaded': model_cache['last_loaded'].isoformat() if model_cache['last_loaded'] else None
    }
    
    if model_cache['product_models']:
        status['product_models_count'] = len(model_cache['product_models'])
    
    return status

@app.route('/single_predict', methods=['GET', 'POST'], endpoint='single_predict')
def single_predict():
    return single_predict_view()

@app.route('/export_results/<item_id>', methods=['POST'])
def export_results(item_id):
    """导出预测结果到Excel文件"""
    try:
        # 获取缓存的预测结果
        results_cache = get_from_cache(f'results_{item_id}')
        if not results_cache:
            flash('没有找到预测结果，请先进行预测分析', 'warning')
            return redirect(url_for('detail_id', item_id=item_id))
        
        # 获取商品信息
        cache_key = 'product_data_list'
        df = get_from_cache(cache_key)
        if df is None:
            flash('商品数据未加载，请重新进入页面', 'warning')
            return redirect(url_for('detail_id', item_id=item_id))
        
        product_data = df[df['商品ID'].astype(str) == str(item_id)]
        if product_data.empty:
            flash('未找到商品信息', 'warning')
            return redirect(url_for('detail_id', item_id=item_id))
        
        product_name = product_data.iloc[0].get('商品名称', f'商品_{item_id}')
        
        # 创建Excel文件
        from openpyxl import Workbook
        from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
        from openpyxl.utils.dataframe import dataframe_to_rows
        import io
        
        wb = Workbook()
        ws = wb.active
        ws.title = "预测结果"
        
        # 设置标题样式
        title_font = Font(name='微软雅黑', size=16, bold=True, color='FFFFFF')
        header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
        cell_font = Font(name='微软雅黑', size=10)
        
        title_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
        header_fill = PatternFill(start_color='8EAADB', end_color='8EAADB', fill_type='solid')
        
        # 添加标题
        ws['A1'] = f"商品库存预测分析报告 - {product_name}"
        ws['A1'].font = title_font
        ws['A1'].fill = title_fill
        ws.merge_cells('A1:J1')
        ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
        
        # 添加基本信息
        ws['A3'] = "商品信息"
        ws['A3'].font = header_font
        ws['A3'].fill = header_fill
        ws.merge_cells('A3:B3')
        
        ws['A4'] = "商品ID"
        ws['B4'] = item_id
        ws['A5'] = "商品名称"
        ws['B5'] = product_name
        
        # 添加预测参数
        ws['D3'] = "预测参数"
        ws['D3'].font = header_font
        ws['D3'].fill = header_fill
        ws.merge_cells('D3:E3')
        
        params = results_cache.get('params', {})
        ws['D4'] = "当前库存"
        ws['E4'] = int(params.get('current_stock', 0))
        ws['D5'] = "预测天数"
        ws['E5'] = int(params.get('days_to_predict', 0))
        ws['D6'] = "补货提前期"
        ws['E6'] = int(params.get('lead_time', 0))
        ws['D7'] = "安全系数"
        ws['E7'] = float(params.get('safety_factor', 0))
        
        # 添加汇总信息
        ws['G3'] = "预测汇总"
        ws['G3'].font = header_font
        ws['G3'].fill = header_fill
        ws.merge_cells('G3:H3')
        
        summary = results_cache.get('summary', {})
        ws['G4'] = "总预测需求"
        ws['H4'] = int(summary.get('total_demand', 0))
        ws['G5'] = "建议补货量"
        ws['H5'] = int(summary.get('total_orders', 0))
        ws['G6'] = "模型类型"
        ws['H6'] = summary.get('model_type', '未知')
        
        # 添加详细预测数据
        ws['A9'] = "详细预测数据"
        ws['A9'].font = header_font
        ws['A9'].fill = header_fill
        ws.merge_cells('A9:J9')
        
        # 表头
        headers = ['阶段', '日期', '预测需求', '库存前', '库存后', '安全库存', '再订货点', '状态', '建议', '补货量']
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=10, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal='center', vertical='center')
        
        # 数据行
        daily_results = results_cache.get('daily', [])
        for row_idx, day_data in enumerate(daily_results, 11):
            ws.cell(row=row_idx, column=1, value=day_data.get('phase_name', ''))
            ws.cell(row=row_idx, column=2, value=day_data.get('date', ''))
            ws.cell(row=row_idx, column=3, value=int(day_data.get('predicted_demand', 0)))
            ws.cell(row=row_idx, column=4, value=int(day_data.get('stock_before', 0)))
            ws.cell(row=row_idx, column=5, value=int(day_data.get('stock_after', 0)))
            ws.cell(row=row_idx, column=6, value=int(day_data.get('safety_stock', 0)))
            ws.cell(row=row_idx, column=7, value=int(day_data.get('reorder_point', 0)))
            ws.cell(row=row_idx, column=8, value=day_data.get('status', ''))
            ws.cell(row=row_idx, column=9, value=day_data.get('suggestion', ''))
            ws.cell(row=row_idx, column=10, value=int(day_data.get('order_qty', 0)))
            
            # 设置单元格样式
            for col in range(1, 11):
                cell = ws.cell(row=row_idx, column=col)
                cell.font = cell_font
                cell.alignment = Alignment(horizontal='center', vertical='center')
        
        # 添加生成时间
        from datetime import datetime
        ws['A' + str(len(daily_results) + 13)] = f"报告生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        ws['A' + str(len(daily_results) + 13)].font = Font(name='微软雅黑', size=9, italic=True)
        
        # 调整列宽
        column_widths = [12, 12, 12, 12, 12, 12, 12, 15, 20, 12]
        for col, width in enumerate(column_widths, 1):
            ws.column_dimensions[chr(64 + col)].width = width
        
        # 添加趋势图（如果有的话）
        if 'plot_url' in results_cache and results_cache['plot_url']:
            try:
                import base64
                from PIL import Image
                import io
                
                # 从base64数据中提取图片数据
                plot_data = results_cache['plot_url']
                if plot_data.startswith('data:image/png;base64,'):
                    image_data = base64.b64decode(plot_data.split(',')[1])
                    
                    # 创建图片对象
                    img = Image.open(io.BytesIO(image_data))
                    
                    # 调整图片大小
                    img = img.resize((600, 300), Image.Resampling.LANCZOS)
                    
                    # 保存到临时文件
                    temp_img_path = os.path.join(app.config['UPLOAD_FOLDER'], 'temp_chart.png')
                    img.save(temp_img_path)
                    
                    # 添加到Excel
                    from openpyxl.drawing.image import Image as XLImage
                    chart_img = XLImage(temp_img_path)
                    chart_img.width = 600
                    chart_img.height = 300
                    
                    # 插入到数据表格下方
                    ws.add_image(chart_img, f'A{len(daily_results) + 15}')
                    
                    # 删除临时文件
                    os.remove(temp_img_path)
                    
            except Exception as e:
                print(f"添加趋势图失败: {e}")
                # 如果添加图片失败，继续执行，不影响其他功能
        
        # 保存到内存
        excel_file = io.BytesIO()
        wb.save(excel_file)
        excel_file.seek(0)
        
        # 生成文件名
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
        filename = f"库存预测报告_{product_name}_{timestamp}.xlsx"
        
        # 返回文件
        from flask import send_file
        return send_file(
            excel_file,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=filename
        )
        
    except Exception as e:
        print(f"导出失败: {e}")
        flash(f'导出失败: {str(e)}', 'danger')
        return redirect(url_for('detail_id', item_id=item_id))

@app.route('/export_single_predict_results', methods=['POST'])
def export_single_predict_results():
    """导出单商品预测结果到Excel文件"""
    try:
        # 从session获取预测结果
        table_data = session.get('single_predict_table')
        summary_data = session.get('single_predict_summary')
        product_info = session.get('single_predict_product_info')
        params_data = session.get('single_predict_params')
        
        if not table_data:
            flash('没有找到预测结果，请先进行预测分析', 'warning')
            return redirect(url_for('single_predict'))
        
        # 创建Excel文件
        from openpyxl import Workbook
        from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
        import io
        
        wb = Workbook()
        ws = wb.active
        ws.title = "单商品预测结果"
        
        # 设置标题样式
        title_font = Font(name='微软雅黑', size=16, bold=True, color='FFFFFF')
        header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
        cell_font = Font(name='微软雅黑', size=10)
        
        title_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
        header_fill = PatternFill(start_color='8EAADB', end_color='8EAADB', fill_type='solid')
        
        # 添加标题
        product_name = product_info.get('商品名称', '未知商品') if product_info else '未知商品'
        product_item_id = product_info.get('货号ID', '未知货号') if product_info else '未知货号'
        ws['A1'] = f"单商品库存预测分析报告 - {product_name}"
        ws['A1'].font = title_font
        ws['A1'].fill = title_fill
        ws.merge_cells('A1:M1')
        ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
        
        # 添加商品信息
        ws['A3'] = "商品信息"
        ws['A3'].font = header_font
        ws['A3'].fill = header_fill
        ws.merge_cells('A3:B3')
        
        if product_info:
            ws['A4'] = "商品ID"
            ws['B4'] = product_info.get('商品ID', '')
            ws['A5'] = "商品名称"
            ws['B5'] = product_info.get('商品名称', '')
            ws['A6'] = "数据区间"
            ws['B6'] = product_info.get('数据区间', '')
            ws['A7'] = "总记录数"
            ws['B7'] = int(product_info.get('总记录数', 0))
        
        # 添加预测参数
        ws['D3'] = "预测参数"
        ws['D3'].font = header_font
        ws['D3'].fill = header_fill
        ws.merge_cells('D3:E3')
        
        if params_data:
            param_mapping = {
                'days': '售卖天数',
                'stock_inventory': '现货库存',
                'procurement_transit': '采购在途',
                'lead_time': '备货周期(天)',
                'return_time': '退货周期(天)',
                'incremental_activity': '预估活动增量',
                'only_return_rate': '仅退款率',
                'shipment_return_rate': '发货退款率',
                'activity_impact': '活动影响率',
                'safety_factor': '安全系数'
            }
            
            row = 4
            for param_key, param_name in param_mapping.items():
                if param_key in params_data:
                    ws[f'D{row}'] = param_name
                    value = params_data[param_key]
                    if isinstance(value, (int, float)):
                        ws[f'E{row}'] = value
                    else:
                        ws[f'E{row}'] = str(value)
                    row += 1
        
        # 添加汇总信息
        ws['G3'] = "预测汇总"
        ws['G3'].font = header_font
        ws['G3'].fill = header_fill
        ws.merge_cells('G3:H3')
        
        if summary_data:
            ws['G4'] = "总预测需求"
            ws['H4'] = int(summary_data.get('total_demand', 0))
            ws['G5'] = "建议补货量"
            ws['H5'] = int(summary_data.get('total_orders', 0))
            ws['G6'] = "模型类型"
            ws['H6'] = summary_data.get('model_type', '单商品Prophet模型')
        
        # 添加详细预测数据
        ws['A10'] = "详细预测数据"
        ws['A10'].font = header_font
        ws['A10'].fill = header_fill
        ws.merge_cells('A10:M10')
        
        # 表头
        headers = ['阶段', '日期', '预测需求', '仅退款数', '实际需求', '售前库存', '售后库存', 
                  '安全库存', '库存状态', '备货建议', '备货到仓数', '当日退货到仓数', '在途库存']
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=11, column=col, value=header)
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = Alignment(horizontal='center', vertical='center')
        
        # 数据行
        for row_idx, day_data in enumerate(table_data, 12):
            ws.cell(row=row_idx, column=1, value=day_data.get('phase_name', ''))
            ws.cell(row=row_idx, column=2, value=day_data.get('date', ''))
            ws.cell(row=row_idx, column=3, value=int(day_data.get('predicted_demand', 0)))
            ws.cell(row=row_idx, column=4, value=int(day_data.get('only_return_num', 0)))
            ws.cell(row=row_idx, column=5, value=float(day_data.get('real_demand', 0)))
            ws.cell(row=row_idx, column=6, value=int(day_data.get('stock_before', 0)))
            ws.cell(row=row_idx, column=7, value=int(day_data.get('stock_after', 0)))
            ws.cell(row=row_idx, column=8, value=int(day_data.get('safety_stock', 0)))
            ws.cell(row=row_idx, column=9, value=day_data.get('status', ''))
            ws.cell(row=row_idx, column=10, value=day_data.get('suggestion', ''))
            ws.cell(row=row_idx, column=11, value=int(day_data.get('delivery_arrived', 0)))
            ws.cell(row=row_idx, column=12, value=int(day_data.get('return_arrived', 0)))
            ws.cell(row=row_idx, column=13, value=int(day_data.get('stock_on_order', 0)))
            
            # 设置单元格样式
            for col in range(1, 14):
                cell = ws.cell(row=row_idx, column=col)
                cell.font = cell_font
                cell.alignment = Alignment(horizontal='center', vertical='center')
        
        # 添加生成时间
        from datetime import datetime
        ws['A' + str(len(table_data) + 14)] = f"报告生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        ws['A' + str(len(table_data) + 14)].font = Font(name='微软雅黑', size=9, italic=True)
        
        # 调整列宽
        column_widths = [12, 12, 12, 12, 12, 12, 12, 12, 15, 20, 15, 15, 12]
        for col, width in enumerate(column_widths, 1):
            ws.column_dimensions[chr(64 + col)].width = width
        
        # 添加趋势图（如果有的话）
        plot_path = session.get('single_predict_plot_path')
        if plot_path and os.path.exists(plot_path):
            try:
                from PIL import Image
                from openpyxl.drawing.image import Image as XLImage
                
                # 直接使用保存的图片文件
                chart_img = XLImage(plot_path)
                chart_img.width = 600
                chart_img.height = 300
                
                # 插入到数据表格下方
                ws.add_image(chart_img, f'A{len(table_data) + 16}')
                
                print(f"成功添加趋势图: {plot_path}")
                
            except Exception as e:
                print(f"添加趋势图失败: {e}")
                # 如果添加图片失败，继续执行，不影响其他功能
        else:
            print(f"未找到图表文件: {plot_path}")
        
        # 保存到内存
        excel_file = io.BytesIO()
        wb.save(excel_file)
        excel_file.seek(0)
        
        # 生成文件名
        timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')

        if product_name =='未找到商品':
            filename = f"商品预测报告_{product_item_id}_{timestamp}.xlsx"
        elif product_item_id == '未知货号':
            filename = f"商品预测报告_{product_name}_{timestamp}.xlsx"
        else:
            filename = f"商品预测报告_{timestamp}.xlsx"
        
        # 清理临时图表文件
        if plot_path and os.path.exists(plot_path):
            try:
                os.remove(plot_path)
                print(f"已清理临时图表文件: {plot_path}")
            except Exception as e:
                print(f"清理临时文件失败: {e}")
        
        # 返回文件
        from flask import send_file
        return send_file(
            excel_file,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=filename
        )
        
    except Exception as e:
        print(f"导出失败: {e}")
        flash(f'导出失败: {str(e)}', 'danger')
        return redirect(url_for('single_predict'))
